package com.knife.generator;

import com.knife.entity.FaultDto;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * create by yongli on 2021-11-08 15:35
 * sql的代码生成研究
 */

public class SqlCodeGen {


    public void test3() {
        String path = "/Users/yongli/Desktop/blade2.xlsx";

        try {
            File file = new File(path);
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            int col = sheet.getRow(0).getPhysicalNumberOfCells();
            int num = sheet.getLastRowNum();

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

//            List<FaultDto> faultDtoList = faultService.list(new QueryWrapper<FaultDto>().eq("source", "电机公司提报"));
            List<FaultDto> faultDtoList = new ArrayList<FaultDto>();
            // 从第2行开始读取
            for (int i = 1; i < num + 1; i++) {

                // 从第二行开始
                Row row = sheet.getRow(i);
                String sql = "update sany_heavy_energy.mm_fault set occurrence_time = \"v1\" where code = \"v2\";";
                //
                Date date = null;
                if (row.getCell(3).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String dates = row.getCell(3).getStringCellValue();
                    date = sdf.parse(dates);
                } else {
                    date = row.getCell(3).getDateCellValue();
                }

                FaultDto faultDto = faultDtoList.get(i - 1);
                sql = sql.replace("v1", sdf.format(date));
                sql = sql.replace("v2", faultDto.getCode());

                System.out.println(sql);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
